package monitora.dao;

import java.io.Serializable;
import java.util.List;

import monitora.bean.Formulario1Bean;
import monitora.dao.Sessao;
import monitora.interfaces.Formulario;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.Restrictions;




public class Formulario1DAO implements Formulario<Formulario1Bean>, Serializable {
	
	@Override
	public boolean adicionar(Formulario1Bean formulario1Bean) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			Transaction transaction = session.beginTransaction();
			 
			session.save(formulario1Bean);
			
			transaction.commit();
			session.close();
			
			return true;
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return false;
	}
	
	@Override
	public boolean atualizar(Formulario1Bean formulario1Bean) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			Transaction transaction = session.beginTransaction();
			
			session.update(formulario1Bean);
			
			transaction.commit();
			session.close();
			
			return true;
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return false;
	}
	
	public boolean existeLoginFormulario1(int codigo_prefeitura) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			Transaction transaction = session.beginTransaction();
			
			List lista = session.createSQLQuery("SELECT * FROM MONITORA.VERIFICA_FORMULARIO1(:PREFEITURA)")
							.setParameter("PREFEITURA", codigo_prefeitura)
							.list();
			//TODO: hell world
			
			
			int resultado = (Integer) lista.get(0);
			
			transaction.commit();
			session.close();
			
			return resultado == 0 ? true : false;
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return false;
	}
	
	@Override
	public Formulario1Bean get(int codigo_prefeitura) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			
			List<Formulario1Bean> lista = session.getNamedQuery("formulario1_codigo")
												.setParameter("CODIGO", codigo_prefeitura)
												.list();
			
			session.close();
			
			if (lista.size() > 0) {
				return lista.get(0);
			}
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return null;
	}
	
	public Formulario1Bean getSemArquivo(int codigo_prefeitura) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			
			List<Formulario1Bean> lista = session.getNamedQuery("formulario1_sem_arquivo")
												.setParameter("CODIGO", codigo_prefeitura)
												.list();
			
			session.close();
			
			if (lista.size() > 0) {
				return lista.get(0);
			}
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return null;
	}
	
	public String getNomeArquivo(int codigo_prefeitura, int codigo_arquivo) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			
			List<String> lista = session.createSQLQuery("SELECT * FROM MONITORA.FORMULARIO1_NOME_ARQUIVO(:PREFEITURA, :CODIGO)")
												.setParameter("PREFEITURA", codigo_prefeitura)
												.setParameter("CODIGO", codigo_arquivo)
												.list();
			
			session.close();
			
			if (lista.size() > 0) {
				return lista.get(0);
			}
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return null;
	}
	
	public byte[] getArquivo(int codigo_prefeitura, int codigo_arquivo) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			
			List<byte[]> lista = session.createSQLQuery("SELECT * FROM MONITORA.FORMULARIO1_ARQUIVO(:PREFEITURA, :CODIGO)")
												.setParameter("PREFEITURA", codigo_prefeitura)
												.setParameter("CODIGO", codigo_arquivo)
												.list();
			
			session.close();
			
			if (lista.size() > 0) {
				return lista.get(0);
			}
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return null;
	}
	
	@Override
	public Formulario1Bean get(String usuario) {
		try {
			return this.get(this.getCodigoLogin(usuario));
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return null;
	}
	
	public int getCodigoLogin(String usuario) {
		return new LoginPrefeituraDAO().getCodigoLogin(usuario);
	}
	
	@Override
	public List<String> filtro(int codigo) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			
			List lista = session.createSQLQuery("SELECT * FROM PREFEITURA.SF_FILTRO_FORMULARIO1(:CODIGO)")
							.setParameter("CODIGO", codigo)
							.list();
			
			session.close();
			
			return lista;
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return null;
	}
	
	public boolean finalizarFormulario1(java.sql.Date data, String nome, String prefeitura) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			Transaction transaction = session.beginTransaction();
			
			List lista = session.createSQLQuery("SELECT * FROM MONITORA.INSERIR_CONFIRMACAO1(:NOME, :DATA, :PREFEITURA)")
							.setParameter("NOME", nome)
							.setParameter("DATA", data)
							.setParameter("PREFEITURA", prefeitura)
							.list();
			
			transaction.commit();
			session.close();
			
			if (lista.size() > 0) {
				return Integer.valueOf(lista.get(0).toString()) == 0 ? true : false;
			}
			
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return false;
	}
	
	public boolean formulario1Finalizado(String prefeitura) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			
			List lista = session.createSQLQuery("SELECT * FROM MONITORA.PREFEITURA_CONFIRMACAO1(:PREFEITURA)")
							.setParameter("PREFEITURA", prefeitura)
							.list();
			
			session.close();
			
			if (lista.size() > 0) {
				return Integer.valueOf(lista.get(0).toString()) == 0 ? true : false;
			}
			
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return false;
	}
	
	public List<String> formulario1Finalizados() {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			
			List<String> lista = session.createSQLQuery("SELECT * FROM MONITORA.PREFEITURA_CONFIRMACAO1()")
							.list();
			
			session.close();
			
			if (lista.size() > 0) {
				return lista;
			}
			
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return null;
	}
	
	public String dadosConfirmacao1(String prefeitura) {
		try {
			SessionFactory sessionFactory = Sessao.getSessionFactory();
			Session session = sessionFactory.openSession();
			
			List<String> lista = session.createSQLQuery("SELECT * FROM MONITORA.DADOS_CONFIRMACAO1(:PREFEITURA)")
							.setParameter("PREFEITURA", prefeitura)
							.list();
			
			session.close();
			
			if (lista.size() > 0) {
				return lista.get(0).toString();
			}
			
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		return null;
	}
	
	public static void main(String[] args) {
		//System.out.println(new Formulario1DAO().getArquivo(3,1).length);
		java.util.Date datau = new java.util.Date();
		java.sql.Date datas = new java.sql.Date(datau.getTime());
		
		java.text.DateFormat df = java.text.DateFormat.getDateInstance(java.text.DateFormat.DEFAULT);
		System.out.println(df.format(datau) + "  " + df.format(datas));
	}
}
